import pymysql
from dbutils.pooled_db import PooledDB
from mysqlUtil.connectmysql import get_db_config

class MySQLConnectionPool(object):
    _instance = None
    _pool = None

    def __new__(cls, *args, **kwargs):
        if cls._instance is None:
            cls._instance = super().__new__(cls)
        return cls._instance

    def __init__(self):
        if self._pool is None:
            config = get_db_config()
            self._pool = PooledDB(
                creator=pymysql,
                maxconnections=10,  # 连接池最大连接数
                mincached=2,        # 初始化时，连接池中至少创建的空闲的链接
                maxcached=5,        # 连接池中最多闲置的链接
                maxshared=3,        # 连接池中最多共享的链接数量
                blocking=True,      # 超过最大连接数量时候的表现
                maxusage=None,      # 一个链接最多被重复使用的次数
                setsession=[],      # 开始会话前执行的命令列表
                host=config.get('host'),
                port=config.get('port'),
                user=config.get('user'),
                password=config.get('password'),
                database=config.get('database'),
                charset='utf8mb4'
            )

    def get_conn(self):
        """获取数据库连接"""
        return self._pool.connection()

    def close_conn(self, conn, cursor=None):
        """关闭连接"""
        if cursor:
            cursor.close()
        if conn:
            conn.close()

# 使用示例
def get_db_connection():
    """获取数据库连接的便捷方法"""
    pool = MySQLConnectionPool()
    return pool.get_conn()

def execute_query(sql, params=None):
    """执行查询的通用方法"""
    pool = MySQLConnectionPool()
    conn = pool.get_conn()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    try:
        cursor.execute(sql, params)
        result = cursor.fetchall()
        return result
    finally:
        pool.close_conn(conn, cursor)

def execute_update(sql, params=None):
    """执行更新的通用方法"""
    pool = MySQLConnectionPool()
    conn = pool.get_conn()
    cursor = conn.cursor()
    try:
        result = cursor.execute(sql, params)
        conn.commit()
        return result
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        pool.close_conn(conn, cursor)
